#!/usr/bin/env python3
# -*- coding:utf-8 -*-
"""
@author: jmz
@file: data_base.py
@time: 2021/3/31 15:20
@desc:
"""
import pymysql

class DataBase:
    def __init__(self):
        user = 'root'
        password = 'root'  # 'zscqbhpt@123'   'root'
        server = 'localhost'  # '172.49.80.175'   'localhost'
        port = 3306
        db = 'hk_bigdata'
        
        try:
            conn = pymysql.connect(
                user=user,
                password=password,
                host=server,
                port=port,
                db=db
            )
            print("数据库连接成功！")
            self.conn = conn
        except pymysql.Error as e:
            print("连接失败", str(e))
    
    def create_tb(self, sql):
        try:
            cursor = self.conn.cursor()  # 获取光标
            cursor.execute(sql)  # 执行sql
            print("创建成功")
        except pymysql.Error as e:
            print("创建失败", str(e))
        finally:
            cursor.close()  # 关闭游标
    
    def insert_tb(self, sql, data):
        try:
            cursor = self.conn.cursor()
            cursor.executemany(sql, data)
            self.conn.commit()
            print("数据插入成功")
            # return True
        except pymysql.Error as e:
            # self.conn.rollback()
            print("插入失败", str(e))
            # return False
        finally:
            cursor.close()
    
    def insert_tb_one(self, sql, data):
        try:
            cursor = self.conn.cursor()
            cursor.execute(sql, data)
            self.conn.commit()
            print("数据插入成功")
            
        except pymysql.Error as e:
            self.conn.rollback()
            print("插入失败", str(e))
            
        finally:
            cursor.close()
    
    def select_tb(self, sql):
        try:
            cursor = self.conn.cursor()
            cursor.execute(sql)
            result1 = cursor.fetchall()
            return result1
        except pymysql.Error as e:
            print("查询失败", sql, str(e))
        finally:
            cursor.close()
    
    def select_tb_one(self, sql):
        try:
            cursor = self.conn.cursor()
            print(cursor.execute(sql))
            result2 = cursor.fetchone()
            print("查询一条结果：", result2)
        except pymysql.Error as e:
            print("查询失败", str(e))
        finally:
            cursor.close()
    
    def select_tb_many(self, sql, count):
        try:
            cursor = self.conn.cursor()
            cursor.execute(sql)
            result3 = cursor.fetchmany(count)
            print("查询结果：", result3)
        except pymysql.Error as e:
            print("查询失败", str(e))
        finally:
            cursor.close()
    
    def update(self, sql):
        try:
            cursor = self.conn.cursor()
            cursor.execute(sql)
            self.conn.commit()
            print("修改成功:", sql)
           
        except pymysql.Error as e:
            print("更新失败", sql, str(e))
            
        finally:
            cursor.close()